package com.bw.dao;

import com.bw.bean.Emp;
import com.bw.bean.Emp1;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;

import java.util.List;

@Mapper
public interface EmpDao {

    @Select("select HOUR(create_time) `name`,count(*) `value` from user_chapter_process group by HOUR(create_time)")
    List<Emp> getList1();


    @Select("select \n" +
            "ti.question_txt `name`,\n" +
            "a.num1,\n" +
            "b.num2,\n" +
            "b.num2/a.num1 `num3`\n" +
            "from \n" +
            "(select question_id,count(question_id) `num1` from test_exam_question group by question_id)a,\n" +
            "(select question_id,count(is_correct) `num2` from test_exam_question where is_correct=1 group by question_id)b,\n" +
            "test_question_info ti\n" +
            "where a.question_id=b.question_id and a.question_id=ti.id")
    List<Emp1> getList2();

    @Select("select \n" +
            "tp.paper_title `name`,\n" +
            "count(te.user_id) `num1`,\n" +
            "avg(te.score) `num2`,\n" +
            "avg(te.duration_sec) `num3`\n" +
            "from test_exam te\n" +
            "LEFT JOIN test_paper tp ON te.paper_id=tp.id\n" +
            "where tp.paper_title is not null \n" +
            "group by tp.paper_title")
    List<Emp1> getList3();

    @Select("select \n" +
            "bs.source_site `name`,\n" +
            "count(*) `value`\n" +
            "from order_info oi\n" +
            "LEFT JOIN order_detail od ON od.order_id=oi.id\n" +
            "LEFT JOIN chapter_info ci ON od.course_id=ci.course_id\n" +
            "LEFT JOIN video_info vi ON vi.chapter_id=ci.id\n" +
            "LEFT JOIN base_source bs ON vi.video_source_id=bs.id\n" +
            "where bs.source_site is not null\n" +
            "group by bs.source_site")
    List<Emp> getList4();

}
